package org.edu.common.uiltst;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;


public class RWxls {
	private static final Logger logger = Logger.getLogger(RWxls.class);

	public static void main(String[] args) throws IOException {
//		List<String[]> result = readXls(
//				"G:\\java3-Job\\HospitalM\\src\\main\\webapp\\xls\\部门信息表.xls",
//				1);
//		// Section sec=null;
//		for (int i = 0; i < result.size(); i++) {
//			// System.out.println(result.get(i));
//			String[] s = result.get(i);
//			for (int j = 0; j < s.length; j++)
//				System.out.print(s[j]);
//			System.out.println();
//
//		}
		 writexls("G:\\各种名单\\test2.xls");
	}

	public static void wxml() {
		
	}

	/**
	 * 
	 * @param fileName
	 *            需要读取的xls 文件
	 * @return
	 * @throws IOException
	 */
	public static List<String[]> readXls(String fileName, int numSheet)
			throws IOException {
		List<String[]> result = new ArrayList<String[]>();

		InputStream is = new FileInputStream(fileName);
		@SuppressWarnings("resource")
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);

		// 循环工作表Sheet
		// for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets();
		// numSheet++) {
		if (numSheet < 0 || numSheet > hssfWorkbook.getNumberOfSheets()) {

			logger.info("参数不合法");

			return null;
		}
		HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
		if (hssfSheet == null) {
			logger.info("参数不合法");
			return null;
		}

		// 循环行Row 第一行为标题不读
		for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
			HSSFRow hssfRow = hssfSheet.getRow(rowNum);
			if (hssfRow == null) {
				continue;
			}

			String[] cellData = new String[6];
			// 循环列Cell
			for (int cellNum = 0; cellNum <= hssfRow.getLastCellNum(); cellNum++) {
				HSSFCell hssfCell = hssfRow.getCell(cellNum);
				if (hssfCell == null) {
					continue;
				}
				// System.out.print("    " + getValue(hssfCell));
				cellData[cellNum] = getValue(hssfCell);
			}
			// if(getValue(hssfRow.getCell(0)).equals("谢茂灼"))
			// continue;
			// try{
			result.add(cellData);
			// }catch(java.lang.NumberFormatException e){
			// e.getMessage();
			// //做页面提示
			// }
			// System.out.println();
		}

		return result;
	}
	
	/**
	 * 
	 * @param fileName
	 *            需要读取的xls 文件
	 * @return
	 * @throws IOException
	 */
	public static List<String[]> readXls(File file, int numSheet)
			throws IOException {
		List<String[]> result = new ArrayList<String[]>();

		InputStream is = new FileInputStream(file);
		@SuppressWarnings("resource")
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
		if (numSheet < 0 || numSheet > hssfWorkbook.getNumberOfSheets()) {
			logger.info("参数不合法");
			return null;
		}
		HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
		if (hssfSheet == null) {
			logger.info("参数不合法");
			return null;
		}
		for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
			HSSFRow hssfRow = hssfSheet.getRow(rowNum);
			if (hssfRow == null) {
				continue;
			}
			String[] cellData = new String[6];
			for (int cellNum = 0; cellNum <= hssfRow.getLastCellNum(); cellNum++) {
				HSSFCell hssfCell = hssfRow.getCell(cellNum);
				if (hssfCell == null) {
					continue;
				}
				cellData[cellNum] = getValue(hssfCell);
			}
			result.add(cellData);
		}

		return result;
	}

	@SuppressWarnings("static-access")
	public static String getValue(HSSFCell hssfCell) {
		DecimalFormat df = new DecimalFormat("#");
		if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(hssfCell.getBooleanCellValue());
		} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
			return String.valueOf(df.format(hssfCell.getNumericCellValue()));
		} else {
			return String.valueOf(hssfCell.getStringCellValue());
		}
	}

	/**
	 * 生成一个Excel文件 jxl
	 * 
	 * @param fileName
	 *            要生成的Excel文件名
	 * @param content
	 *            内容
	 * @jxl.jar 版本：2.6
	 */
	public static void writexls(String fileName) {
		WritableWorkbook wwb = null;
		try {
			// 首先要使用Workbook类的工厂方法创建一个可写入的工作薄(Workbook)对象
			wwb = Workbook.createWorkbook(new File(fileName));

		} catch (IOException e) {
			e.printStackTrace();
		}
		if (wwb != null) {
			// 创建一个可写入的工作表
			// Workbook的createSheet方法有两个参数，第一个是工作表的名称，第二个是工作表在工作薄中的位置
			WritableSheet ws = wwb.createSheet("sheet1", 0);

			for (int i = 0; i < 10; i++) {
				for (int j = 0; j < 5; j++) {
					// 这里需要注意的是，在Excel中，第一个参数表示列，第二个表示行
					 Label labelC = new Label(j, i, "这是第" + (i + 1) + "行，第"
					 + (j + 1) + "列");
					//Label labelC = new Label(j, i, content.get(i)[j]);
					try {
						// 将生成的单元格添加到工作表中
						ws.addCell(labelC);
					} catch (RowsExceededException e) {
						e.printStackTrace();
					} catch (WriteException e) {
						e.printStackTrace();
					}
				}
			}
			try {
				// 从内存中写入文件中
				wwb.write();
				// 关闭资源，释放内存
				wwb.close();
			} catch (IOException e) {
				e.printStackTrace();
			} catch (WriteException e) {
				e.printStackTrace();
			}
		}

	}
	

}
